﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using LoanCalculationService.Objects;
using Utils;


namespace AdapterLayer
{
	public static class QuoteAdapter
	{
		private static string getConnectionString()
		{
			return ConfigurationManager.AppSettings["DBConnection"];
		}

		private static SqlParameter getSqlInputParameter(string pParameterName, object pParameterValue, SqlDbType pParameterType)
		{
			SqlParameter param = new SqlParameter();
			param.ParameterName = pParameterName;
			param.Direction = ParameterDirection.Input;
			param.SqlDbType = pParameterType;
			param.Value = pParameterValue;

			return param;
		}

		internal static LoanCalculationService.Objects.Quote getQuoteByID(int pQuoteID)
		{
			Quote quote = new Quote();
			SqlConnection connection = new SqlConnection(getConnectionString());
			try
			{
				string commandtext = "SELECT * FROM [dbo].[tblQuote] where QuoteID = @pQuoteID";
				SqlCommand command = new SqlCommand(commandtext, connection);
				command.Parameters.Add( getSqlInputParameter( "pQuoteID", pQuoteID, SqlDbType.Int ));
				connection.Open();
				SqlDataReader reader = command.ExecuteReader();

				while (reader.Read())
				{
					quote = new Quote(
						Utilities.getInt32(reader["pQuoteID"])
						,Utilities.getInt32(reader["pCustomerID"])
						,Utilities.getString(reader["pPrptyType"])
						,Utilities.getDecimal(reader["pPrptySize"])
						,Utilities.getInt32(reader["pPrptyYearBuilt"])
						,Utilities.getString(reader["pPrptyAddressLine1"])
						,Utilities.getString(reader["pPrptyAddressLine2"])
						,Utilities.getString(reader["pPrptySuburb"])
						,Utilities.getString(reader["pPrptyState"])
						,Utilities.getInt32(reader["pPrptyPostcode"])
						,Utilities.getDecimal(reader["pPrptyValue"])
						,Utilities.getDecimal(reader["pLoanAmount"])
						,Utilities.getInt32(reader["pLoanTerm"])
						,Utilities.getDecimal(reader["pLoanIntrest"])
						,Utilities.getString(reader["pLoanInsurance"])
						,Utilities.getDecimal(reader["pLoanMRepayment"])
						,Utilities.getDateTime(reader["pQuoteDate"])
					   );
					break;
				}
				connection.Close();
				return quote;
			}
			catch (Exception ex)
			{
				connection.Close();
				return null;
			}
		}

		internal static int updateQuote(int pQuoteID, int pCustomerID, string pPrptyType, decimal pPrptySize, int pPrptyYearBuilt, string pPrptyAddressLine1, string pPrptyAddressLine2, string pPrptySuburb, string pPrptyState, int pPrptyPostcode, decimal pPrptyValue, decimal pLoanAmount, int pLoanTerm, decimal pLoanIntrest, string pLoanInsurance, decimal pLoanMRepayment, DateTime pQuoteDate, out string pResultQuoteNumber)
		{
			// UPDATE
			if (pQuoteID > 0)
			{
				SqlConnection connection = new SqlConnection(getConnectionString());
				StringBuilder commandtext = new StringBuilder();
				try
				{

					commandtext.Append(" UPDATE [dbo].[tblQuote]");
					commandtext.Append(@" SET (QuoteID = @pQuoteID,
						CustomerID = @pCustomerID,
						PrptyType = @pPrptyType,
						PrptySize = @pPrptySize,
						PrptyYearBuilt = @pPrptyYearBuilt,
						PrptyAddressLine1 = @pPrptyAddressLine1,
						PrptyAddressLine2 = @pPrptyAddressLine2,
						PrptySuburb = @pPrptySuburb,
						PrptyState = @pPrptyState,
						PrptyPostcode = @pPrptyPostcode,
						PrptyValue = @pPrptyValue,
						LoanAmount = @pLoanAmount,
						LoanTerm = @pLoanTerm,
						LoanIntrest = @pLoanIntrest,
						LoanInsurance = @pLoanInsurance,
						LoanMRepayment = @pLoanMRepayment,
						QuoteDate = @pQuoteDate)");
					commandtext.Append(" WHERE QuoteID = @pQuoteID");
					SqlCommand command = new SqlCommand(commandtext.ToString(), connection);

					command.Parameters.Add(getSqlInputParameter("@pCustomerID", pCustomerID, SqlDbType.Int));
					command.Parameters.Add(getSqlInputParameter("@pPrptyType", pPrptyType, SqlDbType.NChar));
					command.Parameters.Add(getSqlInputParameter("@pPrptySize", pPrptySize, SqlDbType.Decimal));
					command.Parameters.Add(getSqlInputParameter("@pPrptyYearBuilt", pPrptyYearBuilt, SqlDbType.Int));
					command.Parameters.Add(getSqlInputParameter("@pPrptyAddressLine1", pPrptyAddressLine1, SqlDbType.NChar));
					command.Parameters.Add(getSqlInputParameter("@pPrptyAddressLine2", pPrptyAddressLine2, SqlDbType.NChar));
					command.Parameters.Add(getSqlInputParameter("@pPrptySuburb", pPrptySuburb, SqlDbType.NChar));
					command.Parameters.Add(getSqlInputParameter("@pPrptyState", pPrptyState, SqlDbType.NChar));
					command.Parameters.Add(getSqlInputParameter("@pPrptyPostcode", pPrptyPostcode, SqlDbType.Int));
					command.Parameters.Add(getSqlInputParameter("@pPrptyValue", pPrptyValue, SqlDbType.Decimal));
					command.Parameters.Add(getSqlInputParameter("@pLoanAmount", pLoanAmount, SqlDbType.Decimal));
					command.Parameters.Add(getSqlInputParameter("@pLoanTerm", pLoanTerm, SqlDbType.Int));
					command.Parameters.Add(getSqlInputParameter("@pLoanIntrest", pLoanIntrest, SqlDbType.Decimal));
					command.Parameters.Add(getSqlInputParameter("@pLoanInsurance", pLoanInsurance, SqlDbType.NChar));
					command.Parameters.Add(getSqlInputParameter("@pLoanMRepayment", pLoanMRepayment, SqlDbType.Decimal));
					command.Parameters.Add(getSqlInputParameter("@pQuoteDate", pQuoteDate, SqlDbType.Date));
					command.Parameters.Add(getSqlInputParameter("@pQuoteID", pQuoteID, SqlDbType.Int));

					connection.Open();

					command.ExecuteNonQuery();

					connection.Close();
				}
				catch (Exception ex)
				{
					connection.Close();
					throw ex;
				}
			}
			else
			{
				// INSERT
				SqlConnection connection = new SqlConnection(getConnectionString());
				StringBuilder commandtext = new StringBuilder();
				try
				{

					commandtext.Append(" INSERT INTO [dbo].[tblQuote]");
					commandtext.Append(@" (
						CustomerID,
						PrptyType ,
						PrptySize ,
						PrptyYearBuilt ,
						PrptyAddressLine1 ,
						PrptyAddressLine2 ,
						PrptySuburb ,
						PrptyState ,
						PrptyPostcode ,
						PrptyValue ,
						LoanAmount ,
						LoanTerm ,
						LoanIntrest ,
						LoanInsurance ,
						LoanMRepayment ,
						QuoteDate)
						VALUES(
						@pCustomerID,
						@pPrptyType,
						@pPrptySize,
						@pPrptyYearBuilt,
						@pPrptyAddressLine1,
						@pPrptyAddressLine2,
						@pPrptySuburb,
						@pPrptyState,
						@pPrptyPostcode,
						@pPrptyValue,
						@pLoanAmount,
						@pLoanTerm,
						@pLoanIntrest,
						@pLoanInsurance,
						@pLoanMRepayment,
						@pQuoteDate)
						)
						");
					SqlCommand command = new SqlCommand(commandtext.ToString(), connection);

					command.Parameters.Add(getSqlInputParameter("@pCustomerID", pCustomerID, SqlDbType.Int));
					command.Parameters.Add(getSqlInputParameter("@pPrptyType", pPrptyType, SqlDbType.NChar));
					command.Parameters.Add(getSqlInputParameter("@pPrptySize", pPrptySize, SqlDbType.Decimal));
					command.Parameters.Add(getSqlInputParameter("@pPrptyYearBuilt", pPrptyYearBuilt, SqlDbType.Int));
					command.Parameters.Add(getSqlInputParameter("@pPrptyAddressLine1", pPrptyAddressLine1, SqlDbType.NChar));
					command.Parameters.Add(getSqlInputParameter("@pPrptyAddressLine2", pPrptyAddressLine2, SqlDbType.NChar));
					command.Parameters.Add(getSqlInputParameter("@pPrptySuburb", pPrptySuburb, SqlDbType.NChar));
					command.Parameters.Add(getSqlInputParameter("@pPrptyState", pPrptyState, SqlDbType.NChar));
					command.Parameters.Add(getSqlInputParameter("@pPrptyPostcode", pPrptyPostcode, SqlDbType.Int));
					command.Parameters.Add(getSqlInputParameter("@pPrptyValue", pPrptyValue, SqlDbType.Decimal));
					command.Parameters.Add(getSqlInputParameter("@pLoanAmount", pLoanAmount, SqlDbType.Decimal));
					command.Parameters.Add(getSqlInputParameter("@pLoanTerm", pLoanTerm, SqlDbType.Int));
					command.Parameters.Add(getSqlInputParameter("@pLoanIntrest", pLoanIntrest, SqlDbType.Decimal));
					command.Parameters.Add(getSqlInputParameter("@pLoanInsurance", pLoanInsurance, SqlDbType.NChar));
					command.Parameters.Add(getSqlInputParameter("@pLoanMRepayment", pLoanMRepayment, SqlDbType.Decimal));
					command.Parameters.Add(getSqlInputParameter("@pQuoteDate", pQuoteDate, SqlDbType.Date));

					connection.Open();

					command.ExecuteNonQuery();

					connection.Close();
				}
				catch (Exception ex)
				{
					connection.Close();
					throw ex;
				}

			}
			pResultQuoteNumber = "FAKE_QUOTE_NO";
			return 0;
		}
	}
}
